package com.zgis.module.db.util;

import com.zgis.module.bas.bean.CxEntity;
import com.zgis.module.bas.bean.CxFld;
import com.zgis.module.bas.constant.CxConsts;
import com.zgis.module.bas.constant.CxErrors;
import com.zgis.module.bas.constant.CxKeys;
import com.zgis.module.bas.util.CxRegex;
import com.zgis.module.bas.util.CxValueUtil;
import com.zgis.module.db.bean.CxUnionTable;
import com.zgis.module.db.interfaces.ICxEntity;
import com.zgis.module.db.interfaces.ICxFld;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 修改记录
 * 23-09-17     zhengchaojun             queryId, queryId0, queryAll, query 等函数支持不返回数据的列，用的是 excols
 */

/**
 * 联合查询表对象集
 */
public class CxUnionTables
{
    private CxEntity           mainEntity;
    private ICxEntity          entityManager;
    private ICxFld             fldManager;
    public  List<CxUnionTable> listTabs;
    public  Map<String, CxUnionTable> mapTabs;
    // 存储每个表的字段列表
    private Map<String, List<CxFld>> tableFldMap;

    public CxUnionTables(CxEntity mainEntity, ICxEntity entityManager, ICxFld fldManager)
    {
        this.mainEntity = mainEntity;
        this.entityManager = entityManager;
        this.fldManager = fldManager;

        listTabs = null;
        mapTabs = null;
        tableFldMap = new HashMap<>();
    }

    /**
     * 根据联合查询的表别名取联合表的信息
     *
     * @param alias 别名
     * @return 联合查询表对象
     * @throws Exception 异常
     */
    public CxUnionTable get(String alias) throws Exception
    {
        return mapTabs.get(alias);
    }

    /**
     * 解析联合查询的表名信息（表名模式）
     *
     * @param strTables 表名信息(支持单表)
     * @throws Exception 异常
     */
    public void parseTables(String strTables) throws Exception
    {
        //a;b:table2;c:table3
        //a			主表别名(主表不需要指定主子类型)
        //b:table2 	b(别名),table2(表名)

        String[]     arrTab = strTables.split(CxConsts.SIMICOLON);
        int          num    = arrTab.length;
        CxUnionTable obj;
        List<CxFld>  flds;
        String[]     arrStr1;
        CxEntity     entity;
        int          i, num0;

        listTabs = new ArrayList<>(num);
        mapTabs = new HashMap<>(num);

        for (i = 0; i < num; i++)
        {
            obj = new CxUnionTable();
            arrStr1 = arrTab[i].split(CxConsts.COLON);
            num0 = arrStr1.length;
            // 主表
            if (i == 0)
            {
                obj.alias = arrStr1[0];
                obj.tabname = mainEntity.getName();
                obj.entity = mainEntity;
                
                // 从fldManager获取主表字段
                flds = fldManager.queryFldList(mainEntity.getName());
                tableFldMap.put(obj.alias, flds);
            }
            // 子表
            else if (num0 == 2)
            {
                entity = entityManager.queryEntity(arrStr1[1]);

                if (CxValueUtil.isEmpty(entity))
                {
                    throw new Exception(CxErrors.errTable + CxConsts.MINUS + arrStr1[1]);
                }

                // 从fldManager获取子表字段
                flds = fldManager.queryFldList(entity.getName());
                
                obj.alias = arrStr1[0];
                obj.tabname = entity.getName();
                obj.entity = entity;
                tableFldMap.put(obj.alias, flds);
            }
            else
                throw new Exception(CxErrors.errTable + CxConsts.MINUS + arrTab[i]);

            // 解析列信息
            listTabs.add(obj);
            mapTabs.put(obj.alias, obj);
        }
    }

    /**
     * 解析联合查询的列信息
     *
     * @param strCols 列信息如：a:*;b:flda,fldb
     * @throws Exception 异常
     */
    public void parseCols(String strCols) throws Exception
    {
        //*                     单表查询取全部列
        //a:*;b:flda,fldb
        //a:*					a表的全部列
        //b:flda as xxx,fldb	b表的flda,fldb

        String[]     arrColinf = strCols.split(CxConsts.SIMICOLON);
        int          num       = arrColinf.length;
        CxUnionTable obj;
        String[]     arrStr1;
        int          i;

        // 遍历所有表a:* 和 b:flda,fldb这2个
        for (i = 0; i < num; i++)
        {
            arrStr1 = arrColinf[i].split(CxConsts.COLON);

            if (arrStr1.length == 1)
            {
                //根据别名找到匹配的表
                obj = mapTabs.get("");
                if (obj == null)
                    throw new Exception(CxErrors.errTable + CxConsts.MINUS + arrColinf[i]);

                //匹配字段
                List<CxFld> allFlds = tableFldMap.get(obj.alias);
                if (arrStr1[0].length() == 0 || arrStr1[0].equals(CxConsts.colsAll))
                {
                    obj.flds = allFlds;
                }
                else
                    obj.flds = SqlUtil.getPartFields1(arrStr1[0], allFlds);
            }
            else if (arrStr1.length == 2)
            {
                //根据别名找到匹配的表
                obj = mapTabs.get(arrStr1[0]);
                if (obj == null)
                    throw new Exception(CxErrors.errTable + CxConsts.MINUS + arrColinf[i]);

                //匹配字段，所有(*)还是部分
                List<CxFld> allFlds = tableFldMap.get(obj.alias);
                if (arrStr1[1].length() == 0 || arrStr1[1].equals(CxConsts.colsAll))
                    obj.flds = allFlds;
                else
                    obj.flds = SqlUtil.getPartFields1(arrStr1[1], allFlds);
            }
        }
    }

    /**
     * 取联合查询的表名（支持单表）
     *
     * @return String
     * @throws Exception 异常
     */
    //取表名
    public String getTabnames()
    {
        //FROM gis.aaaa a,gis.bbb b

        String       ret = "";
        int          num = listTabs.size();
        CxUnionTable obj;
        int          i;
        boolean      noAlias;

        for (i = 0; i < num; i++)
        {
            obj = listTabs.get(i);
            noAlias = obj.alias.isEmpty();
            ret += (ret.length() == 0 ? "" : ",") + obj.tabname + (noAlias ? "" : " " + obj.alias);
        }

        return ret;
    }

    /**
     * 取查询返回的列信息
     *
     * @param databaseId 数据库ID
     * @param bGeoCol    是否返回空间字段
     * @return 列信息
     * @throws Exception 异常
     */
    public String getColnames(String databaseId, boolean bGeoCol)
    {
        //a.id,a.fdla,a.fldb,b.fldc,b.fldd					前面有,是因为还要加a.id
        //a.id id,a.flda aa,a.fldb ab,b.fld bc,b.fldd bd	字段别名
        String       ret = "";
        CxUnionTable obj;
        CxFld        fld;
        int          num, fldNum;
        int          i, j;
        boolean      noAlias;

        for (i = 0, num = listTabs.size(); i < num; i++)
        {
            obj = listTabs.get(i);
            noAlias = obj.alias.isEmpty();

            if (i == 0)
                ret = (noAlias ? "" : obj.alias + CxConsts.DOT) + CxKeys.id;

            if (obj.flds == null)
                continue;

            fldNum = obj.flds.size();
            for (j = 0; j < fldNum; j++)
            {
                fld = obj.flds.get(j);
                if (fld.getColname().equals(CxKeys.geom))
                {
                    if (bGeoCol)
                        ret += "," + getGeomCol((noAlias ? "" : obj.alias + CxConsts.DOT), databaseId) + " " + CxKeys.geom;
                }
                else
                    ret += "," + (noAlias ? "" : obj.alias + CxConsts.DOT) + fld.getColname() + (fld.alias != null ? " " + fld.alias : "");
            }
        }

        // 如果联合查询的表大于2个，有相同字段则生成别名
        if(num >= 2)
        {
            ret = generateAliases(ret);
        }
        return ret;
    }

    /**
     * 取查询返回的列信息
     *
     * @param flds       字段列表
     * @param databaseId 数据库ID
     * @param bGeoCol    是否返回空间字段
     * @return 列信息
     */
    public String getColnames(List<CxFld> flds, String databaseId, boolean bGeoCol)
    {
        String ret = "";
        CxFld  fld;

        for (int i = 0, fldNum = flds.size(); i < fldNum; i++)
        {
            if (i == 0)
                ret = CxKeys.id;

            fld = flds.get(i);
            if (fld.getColname().equals(CxKeys.geom))
            {
                if (bGeoCol)
                    ret += "," + getGeomCol("", databaseId) + " " + CxKeys.geom;
            }
            else
                ret += "," + fld.getColname() + (fld.alias != null ? " " + fld.alias : "");
        }

        return ret;
    }

    /**
     * 取空间数据列
     *
     * @param alias      表别名
     * @param databaseId 数据库ID
     * @return 列信息
     */
    public String getGeomCol(String alias, String databaseId)
    {
        String col = null;
        switch (databaseId)
        {
            case "oracle":
                //col = "SDO_UTIL.TO_WKTGEOMETRY(" + alias + "geom)";
                col = "DZ_WKT_MAIN.SDO2WKT(" + alias + "geom)";
                break;
            case "mysql":
            case "postgresql":
                col = "ST_AsText(" + alias + "geom)";
                break;
            case "dm8":
                col = "dmgeo.ST_AsText(" + alias + "geom)";
                break;
            case "sqlserver":
                col = alias + "geom.STAsText()";
                break;
        }
        return col;
    }

    /**
     * 取查询返回的列信息
     *
     * @param bGeoCol  是否返回空间字段
     * @param geomTmpl 空间数据模板
     * @return String
     * @throws Exception 异常
     */
    public String getColnames(boolean bGeoCol, String geomTmpl) throws Exception
    {
        //a.id,a.fdla,a.fldb,b.fldc,b.fldb			前面有,是因为还要加a.id
        String       ret = "";
        int          num = listTabs.size();
        CxUnionTable obj;
        CxFld        fld;
        int          fldNum;
        int          i, j;
        boolean      noAlias;

        for (i = 0; i < num; i++)
        {
            obj = listTabs.get(i);
            noAlias = obj.alias.isEmpty();

            if (i == 0)
                ret = (noAlias ? "" : obj.alias + CxConsts.DOT) + CxKeys.id;

            if (obj.flds == null)
                continue;

            fldNum = obj.flds.size();
            for (j = 0; j < fldNum; j++)
            {
                fld = obj.flds.get(j);
                ret += "," + (noAlias ? "" : obj.alias + CxConsts.DOT) + fld.getColname();        //考虑了联合查询的字段别名
            }
        }

        if (bGeoCol)
        {
            obj = listTabs.get(0);
            List<CxFld> flds = tableFldMap.get(obj.alias);
            if (flds != null && !flds.isEmpty())
            {
                ret += "," + geomTmpl.replaceAll("\\{alias\\}", obj.alias);
            }
        }

        return ret;
    }

    /**
     * 联合统计列信息
     *
     * @param cols 列描述(如: count(*) as total,max(a) as max)
     * @param sep  分隔符
     * @return String            返回列信息(如: COUNT(*) total,max(a) max)
     * @throws Exception 异常
     */
    public String statCols(String cols, String sep) throws Exception
    {
        String       ret     = "";
        String[]     arrCols = cols.split(sep);
        String[]     arrCol1, arrCol2;
        String       col, alias;
        CxUnionTable tab;
        List<CxFld>  flds;
        CxFld        fld;
        Matcher      match;
        String       str, colname;
        byte         found;
        int          i, il, j, jl;

        for (i = 0, il = arrCols.length; i < il; i++)
        {
            arrCol1 = arrCols[i].split(" as ");
            col = arrCol1[0];
            alias = arrCol1.length == 2 ? arrCol1[1] : null;
            found = 0;

            if ((col.indexOf(" ") >= 0) || (col.indexOf("=") >= 0))
                throw new Exception(CxErrors.errCol + CxConsts.MINUS + col);

            //验证格式: count(*), count(fld), count(a.fld)
            match = CxRegex.refStatPtn.matcher(col);
            if (match.find())
            {
                str = match.group();
                colname = str.substring(1, str.length() - 1);

                //需要验证列名是不是存在
                if (colname.equals(CxConsts.colsAll))
                    found = 1;
                else
                {
                    //判断是不是存在 a.fld 格式
                    arrCol2 = colname.split("\\.");

                    if (arrCol2.length == 2)
                    {
                        tab = get(arrCol2[0]);
                        str = arrCol2[1];
                    }
                    else
                    {
                        tab = get("");
                        str = colname;
                    }

                    flds = tableFldMap.get(tab.alias);
                    for (j = 0, jl = flds.size(); j < jl; j++)
                    {
                        fld = flds.get(j);
                        if (fld.getColname().equals(str))
                        {
                            found = 1;
                            break;
                        }
                    }
                }
            }
            else
                throw new Exception(CxErrors.errCol + CxConsts.MINUS + col);

            if (found == 0)
                throw new Exception(CxErrors.errCol + CxConsts.MINUS + col);

            ret += col + (alias != null && !alias.isEmpty() ? " " + alias : "");
        }

        return ret;
    }

    /**
     * 取某字段的类型(支持单表的列、带别名的列 p.no)
     *
     * @param colname 列名
     * @return int                字段类型
     * @throws Exception 异常
     */
    public int getColType(String colname) throws Exception
    {
        String       name = colname;
        String[]     arrCol;
        CxUnionTable table;
        CxFld        fld;

        arrCol = name.split("\\.");
        if (arrCol.length == 2)
        {
            table = mapTabs.get(arrCol[0]);
            name = arrCol[1];
        }
        else
            table = mapTabs.get("");

        // 从tableFldMap中查找字段
        List<CxFld> flds = tableFldMap.get(table.alias);
        fld = null;
        if (flds != null) {
            for (CxFld f : flds) {
                if (f.getColname().equals(name)) {
                    fld = f;
                    break;
                }
            }
        }
        
        if (fld == null)
        {
            if (name.compareToIgnoreCase(CxKeys.id) == 0)
                return CxConsts.DATA_LONG;
            throw new Exception(CxErrors.errCol + CxConsts.MINUS + colname);
        }

        return fld.getType();
    }
    
    /**
     * 检查表中是否存在指定字段
     *
     * @param table 表对象
     * @param fieldName 字段名
     * @return boolean
     */
    public boolean hasField(CxUnionTable table, String fieldName)
    {
        List<CxFld> flds = tableFldMap.get(table.alias);
        if (flds == null) {
            return false;
        }
        
        for (CxFld fld : flds) {
            if (fld.getColname().equals(fieldName)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 给定义的字段生成别名
     *
     * @param col 如  aa.id,aa.bz,aa.scgm,bb.id,bb.bz,bb.scgm,cc.id,cc.bz
     * @return       aa.id,aa.bz,aa.scgm,bb.id AS id_bb,bb.bz AS bz_bb,bb.scgm AS scgm_bb,cc.id AS id_cc,cc.bz AS bz_cc
     */
    public String generateAliases(String col)
    {
        // 正则表达式匹配字段名，包括表别名和字段名
        Pattern pattern = Pattern.compile("\\b([a-zA-Z0-9_]+)\\.(\\w+)\\b");
        Matcher matcher = pattern.matcher(col);

        // 用于存储字段名及其别名
        Map<String, Integer> fieldCount = new HashMap<>();

        StringBuffer sb = new StringBuffer();
        while (matcher.find())
        {
            String tableName = matcher.group(1);
            String fieldName = matcher.group(2);

            // 构建唯一字段名（字段名）
            String uniqueFieldName = fieldName;

            // 计算该字段出现的次数
            int count = fieldCount.getOrDefault(uniqueFieldName, 0);
            fieldCount.put(uniqueFieldName, count + 1);

            // 如果字段出现多次，则添加别名
            if (count > 0)
            {
                String alias = fieldName + "_" + tableName;
                matcher.appendReplacement(sb, tableName + "." + fieldName + " AS " + alias);
            }
            else
            {
                // 如果字段是第一次出现，我们不需要添加别名，但需要记录它的出现
                matcher.appendReplacement(sb, tableName + "." + fieldName);
            }
        }
        matcher.appendTail(sb);

        return sb.toString();
    }
}
